--    Program: 		CAPARIMP_INVOICE.sql
--    Description:  Import AR Invoice information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARD1 DISABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARU1 DISABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_BRI1 DISABLE;
ALTER TRIGGER RA_CUSTOMER_TRX_BRI DISABLE;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_customer_trx_id		number;
   
   --COUNTER					NUMBER;
   
   cursor cur_invoice is
      select * from CRST_RA_CUSTOMER_TRX_ALL
      --WHERE CUSTOMER_TRX_ID = 11830
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_invoice loop   	
     l_err_msg_tmp := '';
     
     
     /*IF COUNTER = 1 THEN
     	DBMS_OUTPUT.PUT_LINE(CUR.CUSTOMER_TRX_ID);
     	COUNTER := 0;
     ENDIF;*/
     
     select RA_CUSTOMER_TRX_S.NEXTVAL
     into   l_customer_trx_id
     from   dual;
     
     update crst_ra_customer_trx_all
     set    customer_trx_id_n = l_customer_trx_id
     where  customer_trx_id = cur.CUSTOMER_TRX_ID;
     
     begin
       -- import data
       insert into RA_CUSTOMER_TRX_ALL 
       (
         CUSTOMER_TRX_ID                 ,
         LAST_UPDATE_DATE                ,
         LAST_UPDATED_BY                 ,
         CREATION_DATE                   ,
         CREATED_BY                      ,
         LAST_UPDATE_LOGIN               ,
         TRX_NUMBER                      ,
         CUST_TRX_TYPE_ID                ,
         TRX_DATE                        ,
         SET_OF_BOOKS_ID                 ,
         BILL_TO_CONTACT_ID              ,
         BATCH_ID                        ,
         BATCH_SOURCE_ID                 ,
         REASON_CODE                     ,
         SOLD_TO_CUSTOMER_ID             ,
         SOLD_TO_CONTACT_ID              ,
         SOLD_TO_SITE_USE_ID             ,
         BILL_TO_CUSTOMER_ID             ,
         BILL_TO_SITE_USE_ID             ,
         SHIP_TO_CUSTOMER_ID             ,
         SHIP_TO_CONTACT_ID              ,
         SHIP_TO_SITE_USE_ID             ,
         SHIPMENT_ID                     ,
         REMIT_TO_ADDRESS_ID             ,
         TERM_ID                         ,
         TERM_DUE_DATE                   ,
         PREVIOUS_CUSTOMER_TRX_ID        ,
         PRIMARY_SALESREP_ID             ,
         PRINTING_ORIGINAL_DATE          ,
         PRINTING_LAST_PRINTED           ,
         PRINTING_OPTION                 ,
         PRINTING_COUNT                  ,
         PRINTING_PENDING                ,
         PURCHASE_ORDER                  ,
         PURCHASE_ORDER_REVISION         ,
         PURCHASE_ORDER_DATE             ,
         CUSTOMER_REFERENCE              ,
         CUSTOMER_REFERENCE_DATE         ,
         COMMENTS                        ,
         INTERNAL_NOTES                  ,
         EXCHANGE_RATE_TYPE              ,
         EXCHANGE_DATE                   ,
         EXCHANGE_RATE                   ,
         TERRITORY_ID                    ,
         INVOICE_CURRENCY_CODE           ,
         INITIAL_CUSTOMER_TRX_ID         ,
         AGREEMENT_ID                    ,
         END_DATE_COMMITMENT             ,
         START_DATE_COMMITMENT           ,
         LAST_PRINTED_SEQUENCE_NUM       ,
         ATTRIBUTE_CATEGORY              ,
         ATTRIBUTE1                      ,
         ATTRIBUTE2                      ,
         ATTRIBUTE3                      ,
         ATTRIBUTE4                      ,
         ATTRIBUTE5                      ,
         ATTRIBUTE6                      ,
         ATTRIBUTE7                      ,
         ATTRIBUTE8                      ,
         ATTRIBUTE9                      ,
         ATTRIBUTE10                     ,
         ORIG_SYSTEM_BATCH_NAME          ,
         POST_REQUEST_ID                 ,
         REQUEST_ID                      ,
         PROGRAM_APPLICATION_ID          ,
         PROGRAM_ID                      ,
         PROGRAM_UPDATE_DATE             ,
         FINANCE_CHARGES                 ,
         COMPLETE_FLAG                   ,
         POSTING_CONTROL_ID              ,
         BILL_TO_ADDRESS_ID              ,
         RA_POST_LOOP_NUMBER             ,
         SHIP_TO_ADDRESS_ID              ,
         CREDIT_METHOD_FOR_RULES         ,
         CREDIT_METHOD_FOR_INSTALLMENTS  ,
         RECEIPT_METHOD_ID               ,
         ATTRIBUTE11                     ,
         ATTRIBUTE12                     ,
         ATTRIBUTE13                     ,
         ATTRIBUTE14                     ,
         ATTRIBUTE15                     ,
         RELATED_CUSTOMER_TRX_ID         ,
         INVOICING_RULE_ID               ,
         SHIP_VIA                        ,
         SHIP_DATE_ACTUAL                ,
         WAYBILL_NUMBER                  ,
         FOB_POINT                       ,
         CUSTOMER_BANK_ACCOUNT_ID        ,
         INTERFACE_HEADER_ATTRIBUTE1     ,
         INTERFACE_HEADER_ATTRIBUTE2     ,
         INTERFACE_HEADER_ATTRIBUTE3     ,
         INTERFACE_HEADER_ATTRIBUTE4     ,
         INTERFACE_HEADER_ATTRIBUTE5     ,
         INTERFACE_HEADER_ATTRIBUTE6     ,
         INTERFACE_HEADER_ATTRIBUTE7     ,
         INTERFACE_HEADER_ATTRIBUTE8     ,
         INTERFACE_HEADER_CONTEXT        ,
         DEFAULT_USSGL_TRX_CODE_CONTEXT  ,
         INTERFACE_HEADER_ATTRIBUTE10    ,
         INTERFACE_HEADER_ATTRIBUTE11    ,
         INTERFACE_HEADER_ATTRIBUTE12    ,
         INTERFACE_HEADER_ATTRIBUTE13    ,
         INTERFACE_HEADER_ATTRIBUTE14    ,
         INTERFACE_HEADER_ATTRIBUTE15    ,
         INTERFACE_HEADER_ATTRIBUTE9     ,
         DEFAULT_USSGL_TRANSACTION_CODE  ,
         RECURRED_FROM_TRX_NUMBER        ,
         STATUS_TRX                      ,
         DOC_SEQUENCE_ID                 ,
         DOC_SEQUENCE_VALUE              ,
         PAYING_CUSTOMER_ID              ,
         PAYING_SITE_USE_ID              ,
         RELATED_BATCH_SOURCE_ID         ,
         DEFAULT_TAX_EXEMPT_FLAG         ,
         CREATED_FROM                    ,
         ORG_ID                          ,
         WH_UPDATE_DATE                  ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE21              ,
         GLOBAL_ATTRIBUTE22              ,
         GLOBAL_ATTRIBUTE23              ,
         GLOBAL_ATTRIBUTE24              ,
         GLOBAL_ATTRIBUTE25              ,
         GLOBAL_ATTRIBUTE26              ,
         GLOBAL_ATTRIBUTE27              ,
         GLOBAL_ATTRIBUTE28              ,
         GLOBAL_ATTRIBUTE29              ,
         GLOBAL_ATTRIBUTE30              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         EDI_PROCESSED_FLAG              ,
         EDI_PROCESSED_STATUS            ,
         MRC_EXCHANGE_RATE_TYPE          ,
         MRC_EXCHANGE_DATE               ,
         MRC_EXCHANGE_RATE               ,
         PAYMENT_SERVER_ORDER_NUM        ,
         APPROVAL_CODE                   ,
         ADDRESS_VERIFICATION_CODE       ,
         OLD_TRX_NUMBER                  ,
         BR_AMOUNT                       ,
         BR_UNPAID_FLAG                  ,
         BR_ON_HOLD_FLAG                 ,
         DRAWEE_ID                       ,
         DRAWEE_CONTACT_ID               ,
         DRAWEE_SITE_USE_ID              ,
         REMITTANCE_BANK_ACCOUNT_ID      ,
         OVERRIDE_REMIT_ACCOUNT_FLAG     ,
         DRAWEE_BANK_ACCOUNT_ID          ,
         SPECIAL_INSTRUCTIONS            ,
         REMITTANCE_BATCH_ID             ,
         PREPAYMENT_FLAG                 ,
         CT_REFERENCE                    ,
         CONTRACT_ID                     ,
         BILL_TEMPLATE_ID
       )
       values
       (       
         l_customer_trx_id                   ,
         cur.LAST_UPDATE_DATE                ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.CREATION_DATE                   ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.LAST_UPDATE_LOGIN               ,
         cur.TRX_NUMBER                      ,
         CAPARIMP_MAPPING.GetTransactionTypeIDMapping(cur.ORG_ID, cur.CUST_TRX_TYPE_ID),
         cur.TRX_DATE                        ,
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
         cur.BILL_TO_CONTACT_ID              ,
         CAPARIMP_MAPPING.GetInvoiceBatchIDMapping(cur.BATCH_ID),
         CAPARIMP_MAPPING.GetTransactionSourceIDMapping(cur.ORG_ID, cur.BATCH_SOURCE_ID),
         cur.REASON_CODE                     ,
         CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.SOLD_TO_CUSTOMER_ID),
         CAPARIMP_MAPPING.GetHZCustAccountRoleIDMapping(cur.SOLD_TO_CONTACT_ID),
         CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.SOLD_TO_SITE_USE_ID),
         CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.BILL_TO_CUSTOMER_ID),
         CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.BILL_TO_SITE_USE_ID),
         CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.SHIP_TO_CUSTOMER_ID),
         CAPARIMP_MAPPING.GetHZCustAccountRoleIDMapping(cur.SHIP_TO_CONTACT_ID),
         CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.SHIP_TO_SITE_USE_ID),
         cur.SHIPMENT_ID                     ,
         CAPARIMP_MAPPING.GetHZCustAcctSiteIDMapping(cur.REMIT_TO_ADDRESS_ID),
         cur.TERM_ID                         ,
         cur.TERM_DUE_DATE                   ,
         cur.PREVIOUS_CUSTOMER_TRX_ID        , 
         CAPARIMP_MAPPING.GetSalesRepIDMapping(cur.ORG_ID, cur.PRIMARY_SALESREP_ID),
         cur.PRINTING_ORIGINAL_DATE          ,
         cur.PRINTING_LAST_PRINTED           ,
         cur.PRINTING_OPTION                 ,
         cur.PRINTING_COUNT                  ,
         cur.PRINTING_PENDING                ,
         cur.PURCHASE_ORDER                  ,
         cur.PURCHASE_ORDER_REVISION         ,
         cur.PURCHASE_ORDER_DATE             ,
         cur.CUSTOMER_REFERENCE              ,
         cur.CUSTOMER_REFERENCE_DATE         ,
         cur.COMMENTS                        ,
         cur.INTERNAL_NOTES                  ,
         cur.EXCHANGE_RATE_TYPE              ,
         cur.EXCHANGE_DATE                   ,
         cur.EXCHANGE_RATE                   ,
         cur.TERRITORY_ID                    ,
         cur.INVOICE_CURRENCY_CODE           ,
         cur.INITIAL_CUSTOMER_TRX_ID         , --NULL
         cur.AGREEMENT_ID                    ,
         cur.END_DATE_COMMITMENT             ,
         cur.START_DATE_COMMITMENT           ,
         cur.LAST_PRINTED_SEQUENCE_NUM       ,
         cur.ATTRIBUTE_CATEGORY              ,
         cur.ATTRIBUTE1                      ,
         cur.ATTRIBUTE2                      ,
         cur.ATTRIBUTE3                      ,
         cur.ATTRIBUTE4                      ,
         cur.ATTRIBUTE5                      ,
         cur.ATTRIBUTE6                      ,
         cur.ATTRIBUTE7                      ,
         cur.ATTRIBUTE8                      ,
         cur.ATTRIBUTE9                      ,
         cur.ATTRIBUTE10                     ,
         cur.ORIG_SYSTEM_BATCH_NAME          ,
         cur.POST_REQUEST_ID                 ,
         cur.REQUEST_ID                      ,
         cur.PROGRAM_APPLICATION_ID          ,
         cur.PROGRAM_ID                      ,
         cur.PROGRAM_UPDATE_DATE             ,
         cur.FINANCE_CHARGES                 ,
         cur.COMPLETE_FLAG                   ,
         cur.POSTING_CONTROL_ID              ,
         CAPARIMP_MAPPING.GetHZCustAcctSiteIDMapping(cur.BILL_TO_ADDRESS_ID),
         cur.RA_POST_LOOP_NUMBER             ,
         CAPARIMP_MAPPING.GetHZCustAcctSiteIDMapping(cur.SHIP_TO_ADDRESS_ID),
         cur.CREDIT_METHOD_FOR_RULES         ,
         cur.CREDIT_METHOD_FOR_INSTALLMENTS  ,
         cur.RECEIPT_METHOD_ID               , 
         cur.ATTRIBUTE11                     ,
         cur.ATTRIBUTE12                     ,
         cur.ATTRIBUTE13                     ,
         cur.ATTRIBUTE14                     ,
         cur.ATTRIBUTE15                     ,
         cur.RELATED_CUSTOMER_TRX_ID         , --NULL
         cur.INVOICING_RULE_ID               ,
         cur.SHIP_VIA                        ,
         cur.SHIP_DATE_ACTUAL                ,
         cur.WAYBILL_NUMBER                  ,
         cur.FOB_POINT                       ,
         CAPBRIMP_MAPPING.GetBankACMapping(cur.CUSTOMER_BANK_ACCOUNT_ID),
         cur.INTERFACE_HEADER_ATTRIBUTE1     ,
         cur.INTERFACE_HEADER_ATTRIBUTE2     ,
         cur.INTERFACE_HEADER_ATTRIBUTE3     ,
         cur.INTERFACE_HEADER_ATTRIBUTE4     ,
         cur.INTERFACE_HEADER_ATTRIBUTE5     ,
         cur.INTERFACE_HEADER_ATTRIBUTE6     ,
         cur.INTERFACE_HEADER_ATTRIBUTE7     ,
         cur.INTERFACE_HEADER_ATTRIBUTE8     ,
         cur.INTERFACE_HEADER_CONTEXT        ,
         cur.DEFAULT_USSGL_TRX_CODE_CONTEXT  ,
         cur.INTERFACE_HEADER_ATTRIBUTE10    ,
         cur.INTERFACE_HEADER_ATTRIBUTE11    ,
         cur.INTERFACE_HEADER_ATTRIBUTE12    ,
         cur.INTERFACE_HEADER_ATTRIBUTE13    ,
         cur.INTERFACE_HEADER_ATTRIBUTE14    ,
         cur.INTERFACE_HEADER_ATTRIBUTE15    ,
         cur.INTERFACE_HEADER_ATTRIBUTE9     ,
         cur.DEFAULT_USSGL_TRANSACTION_CODE  ,
         cur.RECURRED_FROM_TRX_NUMBER        ,
         cur.STATUS_TRX                      ,
         cur.DOC_SEQUENCE_ID                 ,
         cur.DOC_SEQUENCE_VALUE              ,
         CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.PAYING_CUSTOMER_ID),
         CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.PAYING_SITE_USE_ID),
         CAPARIMP_MAPPING.GetTransactionSourceIDMapping(cur.ORG_ID, cur.RELATED_BATCH_SOURCE_ID),
         cur.DEFAULT_TAX_EXEMPT_FLAG         ,
         cur.CREATED_FROM                    ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.WH_UPDATE_DATE                  ,
         cur.GLOBAL_ATTRIBUTE1               ,
         cur.GLOBAL_ATTRIBUTE2               ,
         cur.GLOBAL_ATTRIBUTE3               ,
         cur.GLOBAL_ATTRIBUTE4               ,
         cur.GLOBAL_ATTRIBUTE5               ,
         cur.GLOBAL_ATTRIBUTE6               ,
         cur.GLOBAL_ATTRIBUTE7               ,
         cur.GLOBAL_ATTRIBUTE8               ,
         cur.GLOBAL_ATTRIBUTE9               ,
         cur.GLOBAL_ATTRIBUTE10              ,
         cur.GLOBAL_ATTRIBUTE11              ,
         cur.GLOBAL_ATTRIBUTE12              ,
         cur.GLOBAL_ATTRIBUTE13              ,
         cur.GLOBAL_ATTRIBUTE14              ,
         cur.GLOBAL_ATTRIBUTE15              ,
         cur.GLOBAL_ATTRIBUTE16              ,
         cur.GLOBAL_ATTRIBUTE17              ,
         cur.GLOBAL_ATTRIBUTE18              ,
         cur.GLOBAL_ATTRIBUTE19              ,
         cur.GLOBAL_ATTRIBUTE20              ,
         cur.GLOBAL_ATTRIBUTE21              ,
         cur.GLOBAL_ATTRIBUTE22              ,
         cur.GLOBAL_ATTRIBUTE23              ,
         cur.GLOBAL_ATTRIBUTE24              ,
         cur.GLOBAL_ATTRIBUTE25              ,
         cur.GLOBAL_ATTRIBUTE26              ,
         cur.GLOBAL_ATTRIBUTE27              ,
         cur.GLOBAL_ATTRIBUTE28              ,
         cur.GLOBAL_ATTRIBUTE29              ,
         cur.GLOBAL_ATTRIBUTE30              ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
         cur.EDI_PROCESSED_FLAG              ,
         cur.EDI_PROCESSED_STATUS            ,
         cur.MRC_EXCHANGE_RATE_TYPE          ,
         cur.MRC_EXCHANGE_DATE               ,
         cur.MRC_EXCHANGE_RATE               ,
         cur.PAYMENT_SERVER_ORDER_NUM        ,
         cur.APPROVAL_CODE                   ,
         cur.ADDRESS_VERIFICATION_CODE       ,
         cur.OLD_TRX_NUMBER                  ,
         cur.BR_AMOUNT                       ,
         cur.BR_UNPAID_FLAG                  ,
         cur.BR_ON_HOLD_FLAG                 ,
         cur.DRAWEE_ID                       ,
         cur.DRAWEE_CONTACT_ID               ,
         cur.DRAWEE_SITE_USE_ID              ,
         cur.REMITTANCE_BANK_ACCOUNT_ID      ,
         cur.OVERRIDE_REMIT_ACCOUNT_FLAG     ,
         cur.DRAWEE_BANK_ACCOUNT_ID          ,
         cur.SPECIAL_INSTRUCTIONS            ,
         cur.REMITTANCE_BATCH_ID             ,
         cur.PREPAYMENT_FLAG                 ,
         cur.CT_REFERENCE                    ,
         cur.CONTRACT_ID                     ,
         cur.BILL_TEMPLATE_ID
       );
       
     --COUNTER := 0;
     
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_INVOICE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/

ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARD1 ENABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARU1 ENABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_BRI1 ENABLE;
ALTER TRIGGER RA_CUSTOMER_TRX_BRI ENABLE;

--exit